﻿using DTO;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Collections;
using System.Data;

namespace DAO
{
    public class LoaiGasDAO
    {
        public static void InsertLoaiGas(LoaiGasDTO loaiGas)
        {
            String strSql = "insert into LoaiGas(TenLoai, Gia, CanNang) values(?, ?, ?)";
            OleDbConnection conn = DataAccess.OpenOleConnection();
            OleDbTransaction tran = conn.BeginTransaction();
            try
            {
                OleDbCommand com = new OleDbCommand(strSql, conn);
                com.Transaction = tran;
                com.Parameters.Add("@TenLoai", OleDbType.VarWChar);
                com.Parameters.Add("@Gia", OleDbType.Double);
                com.Parameters.Add("@CanNang", OleDbType.Double);

                com.Parameters["@TenLoai"].Value = loaiGas.TenLoai;
                com.Parameters["@Gia"].Value = loaiGas.Gia;
                com.Parameters["@CanNang"].Value = loaiGas.CanNang;
                com.ExecuteNonQuery();
                tran.Commit();
                strSql = "Select @@IDENTITY as ID";
                com = new OleDbCommand(strSql, conn);
                com.Transaction = tran;
                loaiGas.Id = int.Parse(com.ExecuteScalar().ToString());                
                conn.Close();
            }
            catch (Exception ex)
            {
                tran.Rollback();
                conn.Close();
                throw;
            }
        }
        public static void InsertLoaiGas(LoaiGasDTO loaiGas, bool replaceIfExist)
        {            
            OleDbConnection conn = DataAccess.OpenOleConnection();
            OleDbTransaction tran = conn.BeginTransaction();
            try
            {
                String strSql = "select* from LoaiGas where TenLoai='" + loaiGas.TenLoai + "'";
                OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn);
                da.SelectCommand.Transaction = tran;
                DataTable dt = new DataTable();
                da.Fill(dt);
                if (dt.Rows.Count == 0)
                {
                    strSql = "insert into LoaiGas(TenLoai, Gia, CanNang) values(?, ?, ?)";
                    OleDbCommand com = new OleDbCommand(strSql, conn);
                    com.Transaction = tran;
                    com.Parameters.Add("@TenLoai", OleDbType.VarWChar);
                    com.Parameters.Add("@Gia", OleDbType.Double);
                    com.Parameters.Add("@CanNang", OleDbType.Double);

                    com.Parameters["@TenLoai"].Value = loaiGas.TenLoai;
                    com.Parameters["@Gia"].Value = loaiGas.Gia;
                    com.Parameters["@CanNang"].Value = loaiGas.CanNang;
                    com.ExecuteNonQuery();
                    tran.Commit();
                    strSql = "Select @@IDENTITY as ID";
                    com = new OleDbCommand(strSql, conn);
                    com.Transaction = tran;
                    loaiGas.Id = int.Parse(com.ExecuteScalar().ToString());
                }                
                conn.Close();
            }
            catch (Exception ex)
            {
                tran.Rollback();
                conn.Close();
                throw;
            }
        }
        //public static void UpdateSoLuongGas(int id, int d)
        //{
        //    try
        //    {
        //        if (id > 0)
        //        {
        //            String strSql = "update LoaiGas set SoLuong=SoLuong+? where ID=?";
        //            String connStr = GetConnString();
        //            OleDbConnection conn = new OleDbConnection(connStr);
        //            conn.Open();
        //            OleDbCommand com = new OleDbCommand(strSql, conn);
        //            com.Parameters.Add("@SoLuong", OleDbType.Integer);
        //            com.Parameters.Add("@ID", OleDbType.Integer);

        //            com.Parameters["@SoLuong"].Value = d;
        //            com.Parameters["@ID"].Value = id;
        //            com.ExecuteNonQuery();
        //            conn.Close();
        //        }
        //    }
        //    catch (Exception ex) { }
        //}
        public static void UpdateLoaiGas(LoaiGasDTO loaiGas)
        {
            String strSql = "update LoaiGas set TenLoai=? where ID=?";
            OleDbConnection conn = DataAccess.OpenOleConnection();
            OleDbTransaction tran = conn.BeginTransaction();            
            try
            {
                OleDbCommand com = new OleDbCommand(strSql, conn);
                com.Transaction = tran;
                com.Parameters.Add("@TenLoai", OleDbType.VarWChar);
                com.Parameters.Add("@ID", OleDbType.Integer);

                com.Parameters["@TenLoai"].Value = loaiGas.TenLoai;
                com.Parameters["@ID"].Value = loaiGas.Id;
                com.ExecuteNonQuery();
                tran.Commit();
                conn.Close();
            }
            catch (Exception ex) {
                tran.Rollback();
                conn.Close();
                throw; 
            }
        }
        public static void DeleteLoaiGas(int id)
        {
            String strSql = "delete from LoaiGas where ID=?";
            OleDbConnection conn = DataAccess.OpenOleConnection();
            OleDbTransaction tran = conn.BeginTransaction();
            try
            {
                OleDbCommand com = new OleDbCommand(strSql, conn);
                com.Transaction = tran;
                com.Parameters.Add("@ID", OleDbType.Integer);

                com.Parameters["@ID"].Value = id;
                com.ExecuteNonQuery();
                tran.Commit();
                conn.Close();
            }
            catch (Exception ex) { tran.Rollback(); conn.Close(); throw; }
        }
    }
}
